#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：python_learning 
@File ：数据表定期备份转存.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2025/7/9 下午2:42 
"""
from typing import Dict, List

import mysql.connector
from mysql.connector import Error
import logging
from datetime import datetime

# 配置日志
logging.basicConfig(
	level=logging.INFO,
	format='%(asctime)s - %(levelname)s - %(message)s',
	handlers=[
		logging.FileHandler('backup.log'),
		logging.StreamHandler()
	]
)


class MySQLBackupManager:
	def __init__(self, host: str, database: str, user: str, password: str, port: int = 3306, tables: List[str] = None):
		"""
		初始化数据库连接参数
		:param tables: 需要备份的表列表（新增参数）
		"""
		self.tables = tables or []
		self.host = host
		self.database = database
		self.user = user
		self.password = password
		self.port = port
		self.connection = None

	def connect(self):
		"""
		连接到MySQL数据库
		"""
		try:
			self.connection = mysql.connector.connect(
				host=self.host,
				database=self.database,
				user=self.user,
				password=self.password,
				port=self.port
			)
			if self.connection.is_connected():
				logging.info("成功连接到MySQL数据库")
				return True
		except Error as e:
			logging.error(f"连接数据库失败: {e}")
			return False

	def disconnect(self):
		"""
		断开数据库连接
		"""
		if self.connection and self.connection.is_connected():
			self.connection.close()
			logging.info("数据库连接已断开")

	def table_exists(self, table_name):
		"""
		检查表是否存在
		"""
		try:
			cursor = self.connection.cursor()
			cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
			result = cursor.fetchone()
			cursor.close()
			return result is not None
		except Error as e:
			logging.error(f"检查表存在性失败: {e}")
			return False

	def get_table_structure(self, table_name):
		"""
		获取表结构
		"""
		try:
			cursor = self.connection.cursor()
			cursor.execute(f"SHOW CREATE TABLE {table_name}")
			result = cursor.fetchone()
			cursor.close()
			return result[1] if result else None
		except Error as e:
			logging.error(f"获取表结构失败: {e}")
			return None

	def create_backup_table(self, source_table, backup_table):
		"""
		创建备份表（复制源表结构）
		"""
		try:
			# 获取源表结构
			create_statement = self.get_table_structure(source_table)
			if not create_statement:
				logging.error(f"无法获取表 {source_table} 的结构")
				return False

			# 修改CREATE语句中的表名
			backup_create_statement = create_statement.replace(
				f"CREATE TABLE `{source_table}`",
				f"CREATE TABLE `{backup_table}`"
			)

			cursor = self.connection.cursor()
			cursor.execute(backup_create_statement)
			self.connection.commit()
			cursor.close()

			logging.info(f"成功创建备份表: {backup_table}")
			return True
		except Error as e:
			logging.error(f"创建备份表失败: {e}")
			return False

	def get_table_columns(self, table_name):
		"""
		获取表的列名
		"""
		try:
			cursor = self.connection.cursor()
			cursor.execute(f"SHOW COLUMNS FROM {table_name}")
			columns = [row[0] for row in cursor.fetchall()]
			cursor.close()
			return columns
		except Error as e:
			logging.error(f"获取表列名失败: {e}")
			return []

	def copy_data_to_backup(self, source_table, backup_table):
		"""
		将源表数据拷贝到备份表
		"""
		try:
			# 获取列名
			columns = self.get_table_columns(source_table)
			if not columns:
				logging.error(f"无法获取表 {source_table} 的列信息")
				return False

			columns_str = ', '.join([f"`{col}`" for col in columns])

			cursor = self.connection.cursor()

			# 使用INSERT INTO ... SELECT语句进行数据拷贝
			insert_query = f"""
            INSERT INTO `{backup_table}` ({columns_str})
            SELECT {columns_str} FROM `{source_table}`
            """

			cursor.execute(insert_query)
			affected_rows = cursor.rowcount
			self.connection.commit()
			cursor.close()

			logging.info(f"成功将 {affected_rows} 行数据从 {source_table} 拷贝到 {backup_table}")
			return True
		except Error as e:
			logging.error(f"数据拷贝失败: {e}")
			return False

	def clear_table(self, table_name):
		"""
		清空表数据
		"""
		try:
			cursor = self.connection.cursor()
			cursor.execute(f"DELETE FROM `{table_name}`")
			affected_rows = cursor.rowcount
			self.connection.commit()
			cursor.close()

			logging.info(f"成功清空表 {table_name}，删除了 {affected_rows} 行数据")
			return True
		except Error as e:
			logging.error(f"清空表失败: {e}")
			return False

	def get_table_row_count(self, table_name):
		"""
		获取表的行数
		"""
		try:
			cursor = self.connection.cursor()
			cursor.execute(f"SELECT COUNT(*) FROM `{table_name}`")
			count = cursor.fetchone()[0]
			cursor.close()
			return count
		except Error as e:
			logging.error(f"获取表行数失败: {e}")
			return 0

	def backup_table(self, source_table: str) -> bool:
		"""
		通用表备份方法
		功能流程：
		1. 表存在性检查 → 2. 数据量验证 → 3. 创建备份表 → 4. 数据迁移 → 5. 清空源表
		"""
		backup_table = f"{source_table}_back"
		logging.info(f"开始处理表: {source_table} => {backup_table}")

		if not self.table_exists(source_table):
			logging.error(f"源表 {source_table} 不存在")
			return False

		if (row_count := self.get_table_row_count(source_table)) == 0:
			logging.info(f"{source_table} 无数据可备份")
			return True

		try:
			# 创建/复用备份表
			if not self.table_exists(backup_table):
				self.create_backup_table(source_table, backup_table)

			# 执行数据迁移
			if not self.copy_data_to_backup(source_table, backup_table):
				return False

			# 验证数据一致性
			if (backup_count := self.get_table_row_count(backup_table)) < row_count:
				logging.warning(f"数据迁移不完整: 源表 {row_count} 行 → 备份表 {backup_count} 行")

			# 清空源表
			self.clear_table(source_table)
			return True

		except Exception as e:
			logging.error(f"表 {source_table} 处理失败: {str(e)}")
			return False

	@staticmethod
	def validate_config(config: dict) -> bool:
		"""验证配置参数完整性"""
		required_keys = {'host', 'user', 'password', 'database', 'tables'}
		return all(key in config for key in required_keys)

	def backup_tables(self, table_list: List[str] = None) -> Dict[str, bool]:
		"""
		增强版批量备份方法
		:param table_list: 优先使用传入的表列表，否则使用配置中的列表
		"""
		targets = table_list or self.tables
		if not targets:
			logging.error("未配置需要备份的表列表")
			return {}

		return {table: self.backup_table(table) for table in targets}

	def backup_completed_plan(self):
		"""
		备份completed_plan表的主要逻辑
		"""
		source_table = "completed_plan"
		backup_table = "completed_plan_back"

		logging.info("开始备份completed_plan表...")

		# 检查源表是否存在
		if not self.table_exists(source_table):
			logging.error(f"源表 {source_table} 不存在")
			return False

		# 检查源表是否有数据
		source_count = self.get_table_row_count(source_table)
		if source_count == 0:
			logging.info(f"源表 {source_table} 为空，无需备份")
			return True

		logging.info(f"源表 {source_table} 有 {source_count} 行数据")

		# 检查备份表是否存在
		if self.table_exists(backup_table):
			logging.info(f"备份表 {backup_table} 已存在，进行增量拼接")
			backup_count_before = self.get_table_row_count(backup_table)
			logging.info(f"备份表当前有 {backup_count_before} 行数据")
		else:
			logging.info(f"备份表 {backup_table} 不存在，将创建新的备份表")
			if not self.create_backup_table(source_table, backup_table):
				return False

		# 将源表数据拷贝到备份表
		if not self.copy_data_to_backup(source_table, backup_table):
			return False

		# 验证数据拷贝是否成功
		backup_count_after = self.get_table_row_count(backup_table)
		logging.info(f"备份表现在有 {backup_count_after} 行数据")

		# 清空源表
		if not self.clear_table(source_table):
			return False

		# 验证源表是否已清空
		source_count_after = self.get_table_row_count(source_table)
		if source_count_after == 0:
			logging.info("源表已成功清空")
		else:
			logging.warning(f"源表清空后仍有 {source_count_after} 行数据")

		logging.info("completed_plan表备份完成！")
		return True


def main():
	"""
	主函数 - 使用提供的数据库配置并执行备份
	"""

	# 创建备份管理器实例
	backup_manager = MySQLBackupManager(**DB_CONFIG)

	try:
		# 连接数据库
		if backup_manager.connect():
			# 执行备份
			# success = backup_manager.backup_completed_plan()

			# 多数据表备份转存
			success = backup_manager.backup_tables(DB_CONFIG['tables'])
			if success:
				logging.info("备份任务执行成功！")
			else:
				logging.error("备份任务执行失败！")
		else:
			logging.error("无法连接到数据库！")

	except Exception as e:
		logging.error(f"程序执行过程中发生错误: {e}")

	finally:
		# 断开数据库连接
		backup_manager.disconnect()


# 数据库连接配置
DB_CONFIG = {
	'host': '127.0.0.1',
	'port': 3306,
	'user': 'root',
	'database': 'sgjt_new',
	'password': '12345',
	'tables': [  # 新增配置表列表
		'completed_info',
		'completed_plan'
	]
}

if __name__ == "__main__":
	# 新增配置验证
	if not MySQLBackupManager.validate_config(DB_CONFIG):
		logging.error("数据库配置参数不完整")
		exit(1)

	main()
